﻿package yii.db{

	import yii.*;
	import phpas3.*;


	public class CDbCommand{

		public var params:Array = new Array();
		private var _connection:CDbConnection;
		private var _statement:PDOStatement;
		private var _paramLog:Array = new Array();
		private var _text:String;
		private var _query;
		private var _fetchMode:Array = [ PDO.FETCH_ASSOC ];

		public function CDbCommand( $connection:CDbConnection, $query=null ){
			this._connection = $connection;
			if( $query is String ){
				this.setText( $query );
			} else {
				for( var index in $query ){
					this[index] = $query[index];
				}
			}
		}





	/**
	 * Executes the SQL statement and returns the value of the first column in the first row of data+
	 * This is a convenient method of {@link query} when only a single scalar
	 * value is needed (e+g+ obtaining the count of the records)+
	 * @param array $params input parameters (name=>value) for the SQL execution+ This is an alternative
	 * to {@link bindParam} and {@link bindValue}+ If you have multiple input parameters, passing
	 * them in this way can improve the performance+ Note that if you pass parameters in this way,
	 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa+
	 * binding methods and  the input parameters this way can improve the performance+
	 * This parameter has been available since version 1+0+10+
	 * @return mixed the value of the first column in the first row of the query result+ False is returned if there is no value+
	 * @throws CException execution failed
	 */
	public function queryScalar( $params:Array=null ):*{
		var $result = this.queryInternal( 'fetchColumn', 0, $params );
		return $result;

//		var $result = this.queryInternal( 'fetchColumn', 0, $params );
//		if( is_resource($result) && get_resource_type($result)==='stream' ){
//			return stream_get_contents($result);
//		} else {
//			return $result;
//		}
	}



	/**
	 * Builds and executes a SQL statement for creating a new DB table+
	 *
	 * The columns in the new  table should be specified as name-definition pairs (e+g+ 'name'=>'string'),
	 * where name stands for a column name which will be properly quoted by the method, and definition
	 * stands for the column type which can contain an abstract DB type+
	 * The {@link getColumnType} method will be invoked to convert any abstract type into a physical one+
	 *
	 * If a column is specified with definition only (e+g+ 'PRIMARY KEY (name, type)'), it will be directly
	 * inserted into the generated SQL+
	 *
	 * @param string $table the name of the table to be created+ The name will be properly quoted by the method+
	 * @param array $columns the columns (name=>definition) in the new table+
	 * @param string $options additional SQL fragment that will be appended to the generated SQL+
	 * @return integer number of rows affected by the execution+
	 * @since 1+1+6
	 */
	public function createTable( $table:String, $columns:Object, $options:String=null ):int{
		return this.setText(this.getConnection().getSchema().createTable($table, $columns, $options)).execute();
	}
	
	
	/**
	 * Builds and executes a SQL statement for creating a new index.
	 * @param string $name the name of the index. The name will be properly quoted by the method.
	 * @param string $table the table that the new index will be created for. The table name will be properly quoted by the method.
	 * @param string $column the column(s) that should be included in the index. If there are multiple columns, please separate them
	 * by commas. The column names will be properly quoted by the method.
	 * @param boolean $unique whether to add UNIQUE constraint on the created index.
	 * @return integer number of rows affected by the execution.
	 * @since 1.1.6
	 */
	public function createIndex( $name:String, $table:String, $column:String, $unique:Boolean=false ):int{
		return this.setText( this.getConnection().getSchema().createIndex( $name, $table, $column, $unique) ).execute();
	}	
	


	/**
	 * Prepares the SQL statement to be executed+
	 * For complex SQL statement that is to be executed multiple times,
	 * this may improve performance+
	 * For SQL statement with binding parameters, this method is invoked
	 * automatically+
	 */
	public function prepare(){
		if( !this._statement ){
			try{
				this._statement = this.getConnection().getPdoInstance().prepare( this.getText() );
				this._paramLog = new Array();
			} catch( $e ){
				throw $e;
//				Yii::log('Error in preparing SQL: '+this.getText(),CLogger::LEVEL_ERROR,'system+db+CDbCommand');
//                $errorInfo = $e instanceof PDOException ? $e.errorInfo : null;
//				throw new CDbException(Yii::t('yii','CDbCommand failed to prepare the SQL statement: {error}',array('{error}'=>$e.getMessage())),(int)$e.getCode(),$errorInfo);
			}
		}
	}




	/**
	 * Executes the SQL statement and returns the first row of the result+
	 * This is a convenient method of {@link query} when only the first row of data is needed+
	 * @param boolean $fetchAssociative whether the row should be returned as an associated array with
	 * column names as the keys or the array keys are column indexes (0-based)+
	 * @param array $params input parameters (name=>value) for the SQL execution+ This is an alternative
	 * to {@link bindParam} and {@link bindValue}+ If you have multiple input parameters, passing
	 * them in this way can improve the performance+ Note that if you pass parameters in this way,
	 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa+
	 * binding methods and  the input parameters this way can improve the performance+
	 * This parameter has been available since version 1+0+10+
	 * @return mixed the first row (in terms of an array) of the query result, false if no result+
	 * @throws CException execution failed
	 */
	public function queryRow( $fetchAssociative:Boolean=true, $params:Array=null ):*{
		return this.queryInternal( 'fetch', $fetchAssociative ? this._fetchMode : PDO::FETCH_NUM, $params );
	}




	/**
	 * Executes the SQL statement and returns all rows+
	 * @param boolean $fetchAssociative whether each row should be returned as an associated array with
	 * column names as the keys or the array keys are column indexes (0-based)+
	 * @param array $params input parameters (name=>value) for the SQL execution+ This is an alternative
	 * to {@link bindParam} and {@link bindValue}+ If you have multiple input parameters, passing
	 * them in this way can improve the performance+ Note that if you pass parameters in this way,
	 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa+
	 * binding methods and  the input parameters this way can improve the performance+
	 * This parameter has been available since version 1+0+10+
	 * @return array all rows of the query result+ Each array element is an array representing a row+
	 * An empty array is returned if the query results in nothing+
	 * @throws CException execution failed
	 */
	public function queryAll( $fetchAssociative:Boolean=true, $params:Array=null ):Array{
		return this.queryInternal( 'fetchAll', $fetchAssociative ? this._fetchMode : PDO::FETCH_NUM, $params );
	}






	/**
	 * @param string $method method of PDOStatement to be called
	 * @param mixed $mode parameters to be passed to the method
	 * @param array $params input parameters (name=>value) for the SQL execution+ This is an alternative
	 * to {@link bindParam} and {@link bindValue}+ If you have multiple input parameters, passing
	 * them in this way can improve the performance+ Note that you pass parameters in this way,
	 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa+
	 * binding methods and  the input parameters this way can improve the performance+
	 * This parameter has been available since version 1+0+10+
	 * @return mixed the method execution result
	 */
	private function queryInternal( $method:String, $mode:*, $params:Array=null ):*{
		$params = array_merge( this.params, $params );
		Yii.log( 'Querying SQL: '+this.getText(),'system.db.CDbCommand' );
		try{
			this.prepare();
			if( $params.length == 0 ){
				this._statement.execute();
			} else {
				this._statement.execute( $params );
			}
			if( $method === '' ){
				var $result = new CDbDataReader( this );
			} else {
				$mode = $mode as Array;
				var $result:* = call_user_func_array( new Array(this._statement, $method), $mode );
				this._statement.closeCursor();
			}
			return $result;
		} catch( $e ){
			throw $e;
		}
	}


	/**
	 * Executes the SQL statement+
	 * This method is meant only for executing non-query SQL statement+
	 * No result set will be returned+
	 * @param array $params input parameters (name=>value) for the SQL execution+ This is an alternative
	 * to {@link bindParam} and {@link bindValue}+ If you have multiple input parameters, passing
	 * them in this way can improve the performance+ Note that if you pass parameters in this way,
	 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa+
	 * binding methods and  the input parameters this way can improve the performance+
	 * This parameter has been available since version 1+0+10+
	 * @return integer number of rows affected by the execution+
	 * @throws CException execution failed
	 */
	public function execute( $params:Array=null ):int{
		var $pars:Array = array_merge( this._paramLog, $params );
		var $par:String = "";
		Yii.log( 'Executing SQL: '+ this.getText() + $par, 'system.db.CDbCommand' );
		try{
			this.prepare();
			if( !$params || $params.length==0 ){
				this._statement.execute();
			} else {
				this._statement.execute( $params );
			}
			var $n:int = this._statement.rowCount();
			return $n;
		} catch( $e ){
			throw $e;
		}
		return 0;
	}






	/**
	 * @return PDOStatement the underlying PDOStatement for this command
	 * It could be null if the statement is not prepared yet+
	 */
	public function getPdoStatement():PDOStatement{
		return this._statement;
	}





        public function buildQuery( $query ){
                var $sql:String = isset($query['distinct']) && $query['distinct'] ? 'SELECT DISTINCT' : 'SELECT';
                $sql +=' '+(isset($query['select']) ? $query['select'] : '*');

                if(isset($query['from'])){
                        $sql += "\nFROM "+ $query['from'];
                } else {
                        //throw new CDbException(Yii::t('yii','The DB query must contain the "from" portion.'));
                }
                if(isset($query['join'])){
                        $sql += "\n"+ ($query['join'] is Array ? $query['join'].join("\n") : $query['join']);
				}
                if(isset($query['where'])){
                        $sql += "\nWHERE "+ $query['where'];
				}
                if(isset($query['group'])){
                        $sql += "\nGROUP BY "+ $query['group'];
                }
                if(isset($query['having'])){
                        $sql += "\nHAVING "+ $query['having'];
                }
                if(isset($query['order'])){
                        $sql += "\nORDER BY "+ $query['order'];
                }
                var $limit:int = isset($query['limit']) ? int($query['limit']) : -1;
                var $offset:int = isset($query['offset']) ? int($query['offset']) : -1;
                if( $limit>=0 || $offset>0 ){
                        $sql = this._connection.getCommandBuilder().applyLimit( $sql, $limit, $offset );
                }
                if(isset($query['union'])){
                        $sql += "\nUNION (\n"+ ($query['union'] is Array ? $query['union'].join("\n) UNION (\n") : $query['union']) +')';
                }
                return $sql;
        }








        public function getConnection():CDbConnection{
                return this._connection;
        }








        public function cancel():void{
                this._statement = null;
        }


        public function getText():String{
                if( this._text == '' && this._query ){
                        this.setText( this.buildQuery(this._query) );
                }
                return this._text;
        }

        public function setText( $value:String ):CDbCommand{
                if( this._connection.tablePrefix !== null && $value!='' ){
                        this._text = $value.replace( '/{{(.*?)}}/', this._connection.tablePrefix +"\1" );
                } else {
                        this._text = $value;
                }
                this.cancel();
                return this;
        }




		public function bindValue( $name, $value, $dataType=null ){
			this.prepare();
			if( $dataType===null ){
				this._statement.bindValue( $name, $value, this._connection.getPdoType(gettype($value)) );
			} else {
				this._statement.bindValue( $name, $value, $dataType );
			}
			this._paramLog[$name] = $value;
			return this;
		}



	}
}